using System;
using System.Collections.Generic;
using System.Text;
using emp.IDAL.SysManage;
using System.Collections;
using emp.Model.SysManage;
using System.Data.SqlClient;
using emp.Utility;
using System.Data;

namespace emp.SqlServerDAL.SysManage
{
    public class PrivilegeKey : IPrivilegeKey
    {
        #region IPrivilegeKey Members

        private const string SQL_SELECT_INFO = " select key_uid,key_name,key_code,key_desc,key_value from system_privilege_key where is_del='0' and key_uid=@Key_ID";
        private const string SQL_SELECT_LIST = " select key_uid,key_name,key_code,key_desc,key_value from system_privilege_key where is_del='0' order by  key_value ";

        private const string SQL_INSERT = " exec p_save_privlKey  @Key_Name, @Key_Code, @Key_Desc, @Key_Value ";
        private const string SQL_UPDATE = " exec p_update_privlKey  @Key_ID, @Key_Name, @Key_Code, @Key_Desc, @Key_Value ";
        private const string SQL_DELETE = " update system_privilege_key set is_del='1' where key_uid=@Key_ID ";

        private const string PARM_KEY_ID = "@Key_ID";
        private const string PARM_NAME = "@Key_Name";
        private const string PARM_CODE = "@Key_Code";
        private const string PARM_DESC = "@Key_Desc";
        private const string PARM_VALUE = "@Key_Value";


        public int Insert(PrivilegeKeyInfo keyInfo)
        {
            SqlParameter[] Parms = GetInsertParameters();
            SetInsertParameters(Parms, keyInfo);

            int iIsSuccess = 0;

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        iIsSuccess = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT, Parms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.SysManage.PrivilegeKey.Insert(PrivilegeKeyInfo keyInfo)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return iIsSuccess;
        }

        public int Update(PrivilegeKeyInfo keyInfo)
        {
            SqlParameter[] itemParms = GetModifyParameters();
            SetModifyParameters(itemParms, keyInfo);
            int iIsSuccess = 0;

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        iIsSuccess = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, itemParms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.SysManage.PrivilegeKey.Modify(PrivilegeKeyInfo keyInfo)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return iIsSuccess;
        }

        public int Delete(int iKeyID)
        {
            SqlParameter itemParms = new SqlParameter(PARM_KEY_ID, SqlDbType.Int, 4, "key_uid");
            itemParms.Value = iKeyID;

            int iIsSuccess = 0;

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        iIsSuccess = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_DELETE, itemParms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.SysManage.PrivilegeKey.Delete(int iKeyID)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return iIsSuccess;
        }

        public IList getKeyList()
        {
            IList keyList = new ArrayList();

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_LIST, null))
            {
                while (rdr.Read())
                {
                    PrivilegeKeyInfo keyInfo = new PrivilegeKeyInfo();
                    keyInfo.keyID = rdr.GetInt32(0);
                    keyInfo.KeyName = rdr.GetString(1);
                    keyInfo.KeyCode = rdr.GetString(2);
                    keyInfo.KeyDesc = rdr.GetString(3);
                    keyInfo.KeyValue = rdr.GetInt32(4);

                    keyList.Add(keyInfo);
                }
            }

            return keyList;
        }

        public IList getKeyInfo(int iKeyID)
        {
            IList keyList = new ArrayList();
            try
            {
                SqlParameter parm = new SqlParameter(PARM_KEY_ID, SqlDbType.Int, 4, "key_uid");
                parm.Value = iKeyID;

                //Execute a query to read the products
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_INFO, parm))
                {
                    while (rdr.Read())
                    {
                        PrivilegeKeyInfo keyInfo = new PrivilegeKeyInfo();
                        keyInfo.keyID = rdr.GetInt32(0);
                        keyInfo.KeyName = rdr.GetString(1);
                        keyInfo.KeyCode = rdr.GetString(2);
                        keyInfo.KeyDesc = rdr.GetString(3);
                        keyInfo.KeyValue = rdr.GetInt32(4);

                        keyList.Add(keyInfo);
                    }
                }
            }
            catch (Exception err)
            {
                string sMethod = "emp.SqlServerDAL.SysManage.Organization.getCurrNode(int iNode_ID)";
                EventsLog.WriteLog(err, sMethod);
            }
            return keyList;
        }

        private static SqlParameter[] GetInsertParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT);
            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARM_NAME, SqlDbType.VarChar, 100),
					new SqlParameter(PARM_CODE, SqlDbType.VarChar, 50),
                    new SqlParameter(PARM_DESC, SqlDbType.VarChar, 300),
					new SqlParameter(PARM_VALUE, SqlDbType.Int, 4)                
                };
                SQLHelper.CacheParameters(SQL_INSERT, parms);
            }
            return parms;
        }

        private static SqlParameter[] GetModifyParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_UPDATE);
            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARM_KEY_ID, SqlDbType.Int, 4),
                    new SqlParameter(PARM_NAME, SqlDbType.VarChar, 100),
					new SqlParameter(PARM_CODE, SqlDbType.VarChar, 50),
                    new SqlParameter(PARM_DESC, SqlDbType.VarChar, 300),
					new SqlParameter(PARM_VALUE, SqlDbType.Int, 4) 
                };
                SQLHelper.CacheParameters(SQL_UPDATE, parms);
            }

            return parms;
        }

        private void SetInsertParameters(SqlParameter[] parms, PrivilegeKeyInfo myKeyInfo)
        {
            parms[0].Value = myKeyInfo.KeyName;
            parms[1].Value = myKeyInfo.KeyCode;
            parms[2].Value = myKeyInfo.KeyDesc;
            parms[3].Value = myKeyInfo.KeyValue;
        }

        private void SetModifyParameters(SqlParameter[] parms, PrivilegeKeyInfo myKeyInfo)
        {
            parms[0].Value = myKeyInfo.keyID;
            parms[1].Value = myKeyInfo.KeyName;
            parms[2].Value = myKeyInfo.KeyCode;
            parms[3].Value = myKeyInfo.KeyDesc;
            parms[4].Value = myKeyInfo.KeyValue;
        }

        #endregion



    }
}
